from pyecharts.charts import Bar
from pyecharts import options as opts
from sc.ZDao import getConnect
from pyecharts.commons.utils import JsCode


def generate_job_count_chart():
    # 连接数据库
    db = getConnect()
    cur = db.cursor()

    # 查询语句 - 获取各城市岗位数量（合并区县数据）
    sql = """
    SELECT 
        CASE 
            WHEN job_loc LIKE '%·%' THEN SUBSTRING_INDEX(job_loc, '·', 1)
            ELSE job_loc
        END AS city,
        COUNT(*) AS job_count
    FROM jobs
    GROUP BY city
    ORDER BY job_count DESC
    LIMIT 15  # 只显示岗位数量前15的城市
    """

    cities = []
    counts = []

    try:
        cur.execute(sql)
        res = cur.fetchall()

        for r in res:
            city = r['city'] if isinstance(r, dict) else r[0]
            count = int(r['job_count'] if isinstance(r, dict) else r[1])

            cities.append(city)
            counts.append(count)

        print("成功获取数据：")
        print("城市列表:", cities)
        print("岗位数量:", counts)

    except Exception as e:
        print(f"数据库操作失败: {e}")
        db.rollback()
        return
    finally:
        if db:
            db.close()

    # 创建柱状图
    bar = Bar(init_opts=opts.InitOpts(
        width="1200px",
        height="600px",
        page_title="各城市岗位数量对比",
        theme="light"  # 使用明亮主题
    ))

    # 添加数据
    bar.add_xaxis(cities)
    bar.add_yaxis(
        series_name="岗位数量",
        y_axis=counts,
        label_opts=opts.LabelOpts(
            is_show=True,
            position="top",
            formatter="{c}个",
            font_size=12,
            color="#333"
        ),
        itemstyle_opts=opts.ItemStyleOpts(
            color=JsCode("""
                function(params) {
                    // 渐变色效果
                    var colorList = [
                        '#c23531','#2f4554','#61a0a8','#d48265','#91c7ae',
                        '#749f83','#ca8622','#bda29a','#6e7074','#546570'
                    ];
                    return colorList[params.dataIndex % colorList.length];
                }
            """)
        )
    )

    # 设置全局选项
    bar.set_global_opts(
        title_opts=opts.TitleOpts(
            title="各城市岗位数量TOP15",
            subtitle="数据来源: xm数据库jobs表 | 统计时间: {0}".format(
                datetime.now().strftime("%Y-%m-%d")),
            pos_left="center",
            title_textstyle_opts=opts.TextStyleOpts(
                font_size=20,
                color="#333",
                font_weight="bold"
            ),
            subtitle_textstyle_opts=opts.TextStyleOpts(
                font_size=14,
                color="#666"
            )
        ),
        xaxis_opts=opts.AxisOpts(
            axislabel_opts=opts.LabelOpts(
                rotate=45,
                font_size=12,
                margin=15,
                color="#666"
            ),
            axisline_opts=opts.AxisLineOpts(
                linestyle_opts=opts.LineStyleOpts(
                    width=2,
                    color="#333"
                )
            ),
            name="城市",
            name_location="middle",
            name_gap=35,
            name_textstyle_opts=opts.TextStyleOpts(
                font_size=14,
                color="#333"
            )
        ),
        yaxis_opts=opts.AxisOpts(
            name="岗位数量",
            name_location="middle",
            name_gap=50,
            axislabel_opts=opts.LabelOpts(
                formatter="{value} 个",
                font_size=12,
                color="#666"
            ),
            splitline_opts=opts.SplitLineOpts(
                is_show=True,
                linestyle_opts=opts.LineStyleOpts(
                    width=1,
                    type_="dashed",
                    color="#eee"
                )
            ),
            axisline_opts=opts.AxisLineOpts(
                linestyle_opts=opts.LineStyleOpts(
                    width=2,
                    color="#333"
                )
            )
        ),
        tooltip_opts=opts.TooltipOpts(
            trigger="axis",
            axis_pointer_type="shadow",
            formatter="{b}<br/>岗位数量: {c}个",
            background_color="rgba(50,50,50,0.7)",
            border_color="#333",
            textstyle_opts=opts.TextStyleOpts(
                color="#fff"
            )
        ),
        datazoom_opts=[
            opts.DataZoomOpts(
                type_="slider",
                xaxis_index=0,
                range_start=0,
                range_end=100,
                orient="horizontal",
                pos_bottom="5%"
            )
        ],
        toolbox_opts=opts.ToolboxOpts(
            is_show=True,
            orient="vertical",
            pos_left="right",
            pos_top="center",
            feature={
                "saveAsImage": {
                    "title": "保存图片",
                    "pixelRatio": 2,
                    "excludeComponents": ["toolbox"]
                },
                "dataView": {
                    "title": "数据视图",
                    "readOnly": True,
                    "lang": ["数据视图", "关闭", "刷新"]
                },
                "magicType": {
                    "title": {
                        "bar": "柱状图",
                        "line": "折线图",
                        "stack": "堆叠图"
                    },
                    "type": ["bar", "line", "stack"]
                },
                "restore": {"title": "重置"}
            }
        ),
        visualmap_opts=opts.VisualMapOpts(
            is_show=True,
            dimension=0,
            series_index=0,
            pieces=[
                {"min": 0, "max": 50, "color": "#FFEFD5"},
                {"min": 50, "max": 100, "color": "#FFDAB9"},
                {"min": 100, "max": 200, "color": "#CD853F"},
                {"min": 200, "max": 500, "color": "#8B4513"},
                {"min": 500, "label": "500+", "color": "#A0522D"}
            ],
            pos_left="left",
            pos_bottom="bottom"
        )
    )

    # 渲染图表
    bar.render("city_job_count.html")
    print("图表已生成: city_job_count.html")


if __name__ == "__main__":
    from datetime import datetime

    generate_job_count_chart()